Chapter 12: Tidy data

library(tidyverse)

Loading tidyverse: ggplot2
Loading tidyverse: tibble
Loading tidyverse: tidyr
Loading tidyverse: readr
Loading tidyverse: purrr
Loading tidyverse: dplyr
Conflicts with tidy packages ---------------------------------
filter(): dplyr, stats
lag():    dplyr, stats

12.2 Tidy data

12.2.1 Exercises

1. Using prose, describe how the variables and observations are organised in each of the sample tables.

table2: The type column indicates whether each row of data contains an observation of the number of cases or the population. This then means that the values for country and year are duplicated across two rows - each observation is split across those two rows.

table3: The rate column in this case is a string that contains two separate values - cases and population. Attempting to perform any computation or aggregation with the data in this format would need this string to be broken into separate numeric variables first.

table4: There are two separate tables, one for the cases variable and the other for population. Within each table the rows correspond to a country and the columns then contain separate observations on the target variable for each given year. This means that there are multiple observations within each row, yet the observations within each table are incomplete.

2. Compute the rate for table2, and table4a + table4b. You will need to perform four operations:

3. Extract the number of TB cases per country per year.
4. Extract the matching population per country per year.
5. Divide cases by population, and multiply by 10000.

6. Store back in the appropriate place.

t2cases <- table2 %>%
  filter(type == "cases") %>%
  select(country, year, cases = count)
t2pop <- table2 %>%
  filter(type == "population") %>%
  select(country, year, pop = count)
t2rate <- t2cases %>%
  left_join(t2pop, by = c("country", "year")) %>%
  mutate(rate = (cases / pop) * 10000)
t2rate

t4rate <- table4a %>%
  left_join(table4b, by = c("country"), suffix = c("cases", "pop")) %>%
  mutate(
    `1999rate` = (`1999cases` / `1999pop`) * 10000,
    `2000rate` = (`2000cases` / `2000pop`) * 10000
  )
t4rate

Which representation is easiest to work with? Which is hardest? Why?

table3 would be worst of all because it requires text processing. table2 required some intermediate steps before putting things back together but results in a generally tidy format. table4a and table4b required some extra arguments to the join function for clarity and ends up putting the calculated rates into two separate columns so it remains untidy.

3. Recreate the plot showing change in cases over time using table2 instead of table1. What do you need to do first?

library(ggplot2)
table2 %>%
  filter(type == "cases") %>%
  ggplot(aes(year, count)) +
    geom_line(aes(group = country), colour = "grey50") +
    geom_point(aes(colour = country)) +
    ylab("cases")

I needed to filter the data to select only the rows that had numbers of cases, and then I also changed the label on the y-axis to correctly describe the variable.

12.3 Spreading and gathering

12.3.3 Exercises

1. Why are gather() and spread() not perfectly symmetrical?

Carefully consider the following example:

stocks <- tibble(
  year   = c(2015, 2015, 2016, 2016),
  half  = c(   1,    2,     1,    2),
  return = c(1.88, 0.59, 0.92, 0.17)
)
stocks %>% 
  spread(year, return)  %>% 
  gather("year", "return", `2015`:`2016`)

(Hint: look at the variable types and think about column names.)

spread() only needs two arguments because it determines the new column names from the values in the key column. All it needs to do is coerce those values to be strings so they can function as variable names. gather() needs to be told the new column names and then which existing variables are to be converted into those key-value columns.

Both spread() and gather() have a convert argument. What does it do?

If convert = TRUE is passed to either function then it will attempt to convert the new variables to an appropriate type. Note that year in the example code above has ended up as a string, because the gather() function had used the 2015 and 2016 variable names as strings. Running the same code with conversion will see it return to being a numeric variable, as shown below.

stocks <- tibble(
  year   = c(2015, 2015, 2016, 2016),
  half  = c(   1,    2,     1,    2),
  return = c(1.88, 0.59, 0.92, 0.17)
)
stocks %>% 
  spread(year, return)  %>% 
  gather("year", "return", `2015`:`2016`, convert = TRUE)

2. Why does this code fail?

table4a %>% 
  gather(1999, 2000, key = "year", value = "cases")

Error: Position must be between 0 and n

The existing variable names are non-syntactic so the arguments need to be surrounded by backticks - see below.

table4a %>% 
  gather(`1999`, `2000`, key = "year", value = "cases")

3. Why does spreading this tibble fail? How could you add a new column to fix the problem?

people <- tribble(
  ~name,             ~key,    ~value,
  #-----------------|--------|------
  "Phillip Woods",   "age",       45,
  "Phillip Woods",   "height",   186,
  "Phillip Woods",   "age",       50,
  "Jessica Cordero", "age",       37,
  "Jessica Cordero", "height",   156
)
people %>%
  spread(key, value)

Error: Duplicate identifiers for rows (1, 3)

The third row in the original dataset has the same name and key as the first row, i.e., the function runs into a situation where there are two possible values to go into the one cell for Phillip Woods’s age column. You could add a column that allows the tidying function to uniquely identify people who might have the same name, e.g., add an address or a unique ID number.

4. Tidy the simple tibble below. Do you need to spread or gather it? What are the variables?

preg <- tribble(
  ~pregnant, ~male, ~female,
  "yes",     NA,    10,
  "no",      20,    12
)
preg %>%
  gather("sex", "n", `male`:`female`)

12.4 Separating and uniting

12.4.3 Exercises

1. What do the extra and fill arguments do in separate()? Experiment with the various options for the following two toy datasets.

?separate

These two arguments allow us to specify what happens with any row(s) that has too many (extra) or too few (fill) pieces to fit the into variables. By default, warnings will be issued in both cases, but we can instead drop extra values or merge them into the final column, and we can specify whether missing values should be filled on the right (final column(s)) or left.

tibble(x = c("a,b,c", "d,e,f,g", "h,i,j")) %>% 
  separate(x, c("one", "two", "three"))

Too many values at 1 locations: 2

tibble(x = c("a,b,c", "d,e,f,g", "h,i,j")) %>% 
  separate(x, c("one", "two", "three"), extra = "drop")

tibble(x = c("a,b,c", "d,e,f,g", "h,i,j")) %>% 
  separate(x, c("one", "two", "three"), extra = "merge")

tibble(x = c("a,b,c", "d,e", "f,g,i")) %>% 
  separate(x, c("one", "two", "three"))

Too few values at 1 locations: 2

tibble(x = c("a,b,c", "d,e", "f,g,i")) %>% 
  separate(x, c("one", "two", "three"), fill = "right")

tibble(x = c("a,b,c", "d,e", "f,g,i")) %>% 
  separate(x, c("one", "two", "three"), fill = "left")

2. Both unite() and separate() have a remove argument. What does it do? Why would you set it to FALSE?

It removes the input column(s) after you’ve separated or united your data. The default is TRUE but you would set it to FALSE if you wanted to retain the original data for any reason, whether that is in case you might need to re-run the function with adjustments or if the original data format is also useful for analysis in some way.

3. Compare and contrast separate() and extract(). Why are there three variations of separation (by position, by separator, and with groups), but only one unite?

?tidyr::extract

extract() lets you specify regular expressions with capturing groups and would support more complex regular expressions to process the values - if the groups don’t match then the data will come out as NA. separate() is the more simple approach that likely works for most circumstances where you just need to specify a separator, and it also separates by position. There are three approaches to separation but only one for uniting because data that requires separation can come in any variety of forms and you need a method that works for the specific form your data comes in - it might have a separator, it might not have separators but have fixed positioning, etc. On the other hand, joining values together can be dealt with in a single method that provides the necessary parameters (e.g., separator, which can also be set to an empty string).

12.5 Missing values

12.5.1 Exercises

1. Compare and contrast the fill arguments to spread() and complete().

?spread
?complete

The fill argument in spread() specifies what any missing values - both explicit and implicit - will be set to in the new columns. By default it will be NA but can be set to any other value. In complete(), the fill argument provides a named list that identifies for each variable whatvalue should be used when an implicit missing value is converted to explicit missing. The same value will also replace existing explicit missing values. Both approaches appear to ensure that implicit missing values become explicit, and they provide the possibility of using an alternative value instead of NA.

2. What does the direction argument to fill() do?

By default it specifies that the fill should be down, ie the missing value should be filled by the nearest value above it, but it can also specify up instead.

12.6 Case Study

12.6.1 Exercises

1. In this case study I set na.rm = TRUE just to make it easier to check that we had the correct values. Is this reasonable? Think about how missing values are represented in this dataset. Are there implicit missing values? What’s the difference between an NA and zero?

who %>%
  gather(code, value, new_sp_m014:newrel_f65) %>% 
  mutate(code = stringr::str_replace(code, "newrel", "new_rel")) %>%
  separate(code, c("new", "type", "sexage")) %>% 
  select(-new, -iso2, -iso3) %>% 
  separate(sexage, c("sex", "age"), sep = 1)

who %>%
  gather(code, value, new_sp_m014:newrel_f65) %>% 
  mutate(code = stringr::str_replace(code, "newrel", "new_rel")) %>%
  separate(code, c("new", "type", "sexage")) %>% 
  select(-new, -iso2, -iso3) %>% 
  separate(sexage, c("sex", "age"), sep = 1) %>%
  complete(country, year, type, sex, age)

The addition of more than 10,000 rows using complete() on the tidy dataset suggests that there were implicit missing values. An NA value indicates that we don’t know how many cases there were within the given combination of country, year, type, sex and age, whereas a zero indicates that there were no observed cases. Although it can be convenient and helpful when producing the data processing commands to strip out the missing values, analysis of the missing data can itself be informative, e.g., were records in some countries more likely to be incomplete?

2. What happens if you neglect the mutate() step? (mutate(key = stringr::str_replace(key, "newrel", "new_rel")))

who %>%
  gather(code, value, new_sp_m014:newrel_f65) %>% 
  # mutate(code = stringr::str_replace(code, "newrel", "new_rel")) %>%
  separate(code, c("new", "type", "sexage")) %>% 
  select(-new, -iso2, -iso3) %>% 
  separate(sexage, c("sex", "age"), sep = 1)

Too few values at 101360 locations: 304081, 304082, 304083, 304084, 304085, 304086, 304087, 304088, 304089, 304090, 304091, 304092, 304093, 304094, 304095, 304096, 304097, 304098, 304099, 304100, ...

The rows that have a missing underscore in the key column (values beginning with newrel rather than new_rel) won’t separate properly as they will put newrel into the "new" variable, the value that should be sexage into the type column and will have nothing left to go into the sexage column.

3. I claimed that iso2 and iso3 were redundant with country. Confirm this claim.

who %>%
  group_by(country, iso2, iso3) %>%
  summarise(count = n())

Inspection of the summarised data shows that each country has a single corresponding iso2 value and a single iso3 value.

4. For each country, year, and sex compute the total number of cases of TB. Make an informative visualisation of the data.

A time-series line plot for each country with separate coloured lines for males and females would be informative about trends over time in each country as well as any sex differences. With more than 200 countries in the dataset a standard small multiples plot using a facet function won’t be readable, so we use the ggforce package’s paginated facet plotting capability. Because there is significant variation (orders of magitude) between countries in the number of cases a uniform scale across the facets’ y-axis would mean each individual subplot isn’t clearly visualised, so the scales = "free_y" argument is used. The pages are generated via a for loop - it takes a considerable time to run but produces plots that are generally clear and informative.

library(ggforce)
who2 <- who %>%
  gather(code, value, new_sp_m014:newrel_f65, na.rm = TRUE) %>% 
  mutate(code = stringr::str_replace(code, "newrel", "new_rel")) %>%
  separate(code, c("new", "type", "sexage")) %>% 
  select(-new, -iso2, -iso3) %>% 
  separate(sexage, c("sex", "age"), sep = 1) %>%
  group_by(country, year, sex) %>%
  summarise(cases = sum(value))
p <- who2 %>%
  ggplot(aes(x = year, y = cases, colour = sex)) +
    geom_line() +
    facet_wrap_paginate("country", ncol = 3, nrow = 3, scales = "free_y", page = 1)
pages <- n_pages(p)
for(i in 1:pages) {
  print(
    who2 %>%
      ggplot(aes(x = year, y = cases, colour = sex)) +
        geom_line() +
        facet_wrap_paginate("country", ncol = 3, nrow = 3, scales = "free_y", page = i)
  )
}